package com.jago;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;

public class ExcelWriteTest {

    private static final String PATH = "D:\\Program Files (x86)\\IDEA-2020.3.4\\workspace\\excel-demo\\excel\\";

    @Test
    public void test03Version() throws Exception {

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("我的工作薄");
        // 创建第一行
        Row row1 = sheet.createRow(0);
        // 创建第一行第一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("日期");

        // 创建第一行第二个单元格
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        // 创建第二行
        Row row2 = sheet.createRow(1);
        // 创建第二行第一个单元格
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("姓名");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("Jago");

        // 写入excel
        FileOutputStream fos = new FileOutputStream(PATH + "poi.xls");
        workbook.write(fos);
        fos.close();
    }

    @Test
    public void test07Version() throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("我的工作薄");
        // 创建第一行
        Row row1 = sheet.createRow(0);
        // 创建第一行第一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("日期");

        // 创建第一行第二个单元格
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        // 创建第二行
        Row row2 = sheet.createRow(1);
        // 创建第二行第一个单元格
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("姓名");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("wtl");

        // 写入excel
        FileOutputStream fos = new FileOutputStream(PATH + "poi.xlsx");
        workbook.write(fos);
        fos.close();
    }

    @Test
    public void test03VersionBigData() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int row = 0; row < 65536; row++) {
            Row rows = sheet.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell cells = rows.createCell(cell);
                cells.setCellValue(cell);
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH + "poi_bigdata.xls");
        workbook.write(fos);
        fos.close();
        double end = ((double) System.currentTimeMillis() - start) / 1000;
        System.out.println("写入结束，总耗时：" + end + "秒");
    }

    @Test
    public void test07VersionBigData() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int row = 0; row < 65537; row++) {
            Row rows = sheet.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell cells = rows.createCell(cell);
                cells.setCellValue(cell);
            }
        }
        FileOutputStream fos = new FileOutputStream("D:\\Program Files (x86)\\IDEA-2020.3.4\\workspace\\excel-demo\\excel\\poi_bigdata.xlsx");
        workbook.write(fos);
        fos.close();
        double end = ((double) System.currentTimeMillis() - start) / 1000;
        System.out.println("写入结束，总耗时：" + end + "秒");
    }

    @Test
    public void test07VersionBigDataSuper() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        for (int row = 0; row < 65537; row++) {
            Row rows = sheet.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell cells = rows.createCell(cell);
                cells.setCellValue(cell);
            }
        }
        FileOutputStream fos = new FileOutputStream(PATH + "poi_bigdata_super.xlsx");
        workbook.write(fos);
        fos.close();
        // 清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        double end = ((double) System.currentTimeMillis() - start) / 1000;
        System.out.println("写入结束，总耗时：" + end + "秒");
    }
}
